/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import entity.Employee;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import utility.DatabaseConnection;

/**
 *
 * @author Admin
 */
public class EmployeeModel {

    private String sql = null;
    private Connection con = null;
    private CallableStatement cs = null;
    private ResultSet rs = null;
    private PreparedStatement ps = null;

    public EmployeeModel() {
    }

    public Employee checkLogin(String username, String password) {
        Employee e = null;
        try {
            con = DatabaseConnection.getConnection();
            sql = "select * from Employee where Username=? and Password=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();
            while (rs.next()) {
                e = new Employee();
                e.setEmployeeId(rs.getString(1));
                e.setDepartmentId(rs.getString(2));
                e.setUsername(rs.getString(3));
                e.setPassword(rs.getString(4));
                e.setFullname(rs.getString(5));
                e.setAddress(rs.getString(6));
                e.setPhone(rs.getString(7));
                e.setEmail(rs.getString(8));
            }
            return e;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public ArrayList<Employee> getAllEmployees() {
        ArrayList<Employee> lst = new ArrayList<Employee>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call getAllEmployee}";
            cs = con.prepareCall(sql);
            rs = cs.executeQuery();
            while (rs.next()) {
                Employee e = new Employee();
                e.setEmployeeId(rs.getString(1));
                e.setDepartmentId(rs.getString(2));
                e.setUsername(rs.getString(3));
                e.setPassword(rs.getString(4));
                e.setFullname(rs.getString(5));
                e.setAddress(rs.getString(6));
                e.setPhone(rs.getString(7));
                e.setEmail(rs.getString(8));
                lst.add(e);
            }

            return lst;
            
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public Employee getOneEmployee(String id){
        Employee e = null;
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call getOneEmployee(?)}";
            cs = con.prepareCall(sql);
            cs.setString(1, id);
            rs = cs.executeQuery();

            while (rs.next()) {
                e = new Employee();
                e.setEmployeeId(rs.getString(1));
                e.setDepartmentId(rs.getString(2));
                e.setUsername(rs.getString(3));
                e.setPassword(rs.getString(4));
                e.setFullname(rs.getString(5));
                e.setAddress(rs.getString(6));
                e.setPhone(rs.getString(7));
                e.setEmail(rs.getString(8));
            }

            return e;
            
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public boolean insertEmp(Employee emp) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call insertEmployee(?,?,?,?,?,?,?,?)}";
            cs = con.prepareCall(sql);
            cs.setString(1, emp.getEmployeeId());
            cs.setString(2, emp.getDepartmentId());
            cs.setString(3, emp.getUsername());
            cs.setString(4, emp.getPassword());
            cs.setString(5, emp.getFullname());
            cs.setString(6, emp.getAddress());
            cs.setString(7, emp.getPhone());
            cs.setString(8, emp.getEmail());
            if (cs.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateEmp(Employee emp) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "update Employee set DepartmentId=?, Username=?, "+
                "Password=?, FullName=?, Address=?, Phone=?, Email=? "+
                "where EmpID=?";
            PreparedStatement ps = con.prepareStatement(sql);

            ps.setString(1, emp.getDepartmentId());
            ps.setString(2, emp.getUsername());
            ps.setString(3, emp.getPassword());
            ps.setString(4, emp.getFullname());
            ps.setString(5, emp.getAddress());
            ps.setString(6, emp.getPhone());
            ps.setString(7, emp.getEmail());
            ps.setString(8, emp.getEmployeeId());

            if (ps.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteEmployee(String id){
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call deleteEmployee(?)}";
            cs = con.prepareCall(sql);
            cs.setString(1, id);

            if (cs.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    //Fill by Username
    public ArrayList<Employee> getEmpByUser(String usernames) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_getEmpByUsername(?)}";
            ArrayList<Employee> listEmp = new ArrayList<Employee>();
            cs = con.prepareCall(sql);
            cs.setString(1, usernames);
            rs = cs.executeQuery();
            while (rs.next()) {
                Employee a = new Employee();
                a.setEmployeeId(rs.getString(1));
                a.setDepartmentId(rs.getString(2));
                a.setUsername(rs.getString(3));
                a.setPassword(rs.getString(4));
                a.setFullname(rs.getString(5));
                a.setAddress(rs.getString(6));
                a.setPhone(rs.getString(7));
                a.setEmail(rs.getString(8));                
                listEmp.add(a);
            }
            rs.close();
            return listEmp;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

     //Fill by Email
    public ArrayList<Employee> getEmpByEmail(String usernames) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "{call sp_getEmpByEmail(?)}";
            ArrayList<Employee> listEmp = new ArrayList<Employee>();
            cs = con.prepareCall(sql);
            cs.setString(1, usernames);
            rs = cs.executeQuery();
            while (rs.next()) {
                Employee a = new Employee();
                a.setEmployeeId(rs.getString(1));
                a.setDepartmentId(rs.getString(2));
                a.setUsername(rs.getString(3));
                a.setPassword(rs.getString(4));
                a.setFullname(rs.getString(5));
                a.setAddress(rs.getString(6));
                a.setPhone(rs.getString(7));
                a.setEmail(rs.getString(8));
                listEmp.add(a);
            }
            rs.close();
            return listEmp;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public ArrayList<Employee> getEmpByDep(String dep) {
        try {
            con = DatabaseConnection.getConnection();
            sql = "select * from Employee where DepartmentID=?";
            ArrayList<Employee> listEmp = new ArrayList<Employee>();
            ps = con.prepareStatement(sql);
            ps.setString(1, dep);
            rs = ps.executeQuery();
            while (rs.next()) {
                Employee a = new Employee();
                a.setEmployeeId(rs.getString(1));
                a.setDepartmentId(rs.getString(2));
                a.setUsername(rs.getString(3));
                a.setPassword(rs.getString(4));
                a.setFullname(rs.getString(5));
                a.setAddress(rs.getString(6));
                a.setPhone(rs.getString(7));
                a.setEmail(rs.getString(8));
                listEmp.add(a);
            }
            rs.close();
            return listEmp;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
